package housing.task3.jdbc;

import housing.task3.dao.HsWorktypeDao;
import housing.task3.dto.HsWorktype;
import housing.task3.dto.PK.HsWorktypePk;
import housing.task3.exceptions.HsWorktypeDaoException;
import org.apache.log4j.Logger;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

public class HsWorktypeDaoImpl extends AbstractDAO implements HsWorktypeDao {
    /**
     * The factory class for this DAO has two versions of the create() method - one that
     * takes no arguments and one that takes a Connection argument. If the Connection version
     * is chosen then the connection will be stored in this attribute and will be used by all
     * calls to this DAO, otherwise a new Connection will be allocated for each operation.
     */
    protected java.sql.Connection userConn;

    protected static final Logger logger = Logger.getLogger(HsWorktypeDaoImpl.class);

    /**
     * All finder methods in this class use this SELECT constant to build their queries
     */
    protected final String SQL_SELECT = "SELECT workType_id, name FROM " + getTableName() + "";

    /**
     * Finder methods will pass this value to the JDBC setMaxRows method
     */
    protected int maxRows;

    /**
     * SQL INSERT statement for this table
     */
    protected final String SQL_INSERT = "INSERT INTO " + getTableName() + " ( workType_id, name ) VALUES ( ?, ? )";

    /**
     * SQL UPDATE statement for this table
     */
    protected final String SQL_UPDATE = "UPDATE " + getTableName() + " SET workType_id = ?, name = ? WHERE workType_id = ?";

    /**
     * SQL DELETE statement for this table
     */
    protected final String SQL_DELETE = "DELETE FROM " + getTableName() + " WHERE workType_id = ?";

    /**
     * Index of column workType_id
     */
    protected static final int COLUMN_WORKTYPE_ID = 1;

    /**
     * Index of column name
     */
    protected static final int COLUMN_NAME = 2;

    /**
     * Number of columns
     */
    protected static final int NUMBER_OF_COLUMNS = 2;

    /**
     * Index of primary-key column workType_id
     */
    protected static final int PK_COLUMN_WORKTYPE_ID = 1;

    /**
     * Inserts a new row in the HS_WorkType table.
     */
    public HsWorktypePk insert(HsWorktype dto) throws HsWorktypeDaoException {
        long t1 = System.currentTimeMillis();
        // declare variables
        final boolean isConnSupplied = (userConn != null);
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {
            // get the user-specified connection or get a connection from the ResourceManager
            conn = isConnSupplied ? userConn : ResourceManager.getConnection();

            stmt = conn.prepareStatement(SQL_INSERT);
            int index = 1;
            stmt.setInt(index++, dto.getWorktypeId());
            stmt.setString(index++, dto.getName());
            if (logger.isDebugEnabled()) {
                logger.debug("Executing " + SQL_INSERT + " with DTO: " + dto);
            }

            int rows = stmt.executeUpdate();
            long t2 = System.currentTimeMillis();
            if (logger.isDebugEnabled()) {
                logger.debug(rows + " rows affected (" + (t2 - t1) + " ms)");
            }

            reset(dto);
            return dto.createPk();
        }
        catch (Exception _e) {
            logger.error("Exception: " + _e.getMessage(), _e);
            throw new HsWorktypeDaoException("Exception: " + _e.getMessage(), _e);
        }
        finally {
            ResourceManager.close(stmt);
            if (!isConnSupplied) {
                ResourceManager.close(conn);
            }

        }

    }

    /**
     * Updates a single row in the HS_WorkType table.
     */
    public void update(HsWorktypePk pk, HsWorktype dto) throws HsWorktypeDaoException {
        long t1 = System.currentTimeMillis();
        // declare variables
        final boolean isConnSupplied = (userConn != null);
        Connection conn = null;
        PreparedStatement stmt = null;

        try {
            // get the user-specified connection or get a connection from the ResourceManager
            conn = isConnSupplied ? userConn : ResourceManager.getConnection();

            if (logger.isDebugEnabled()) {
                logger.debug("Executing " + SQL_UPDATE + " with DTO: " + dto);
            }

            stmt = conn.prepareStatement(SQL_UPDATE);
            int index = 1;
            stmt.setInt(index++, dto.getWorktypeId());
            stmt.setString(index++, dto.getName());
            stmt.setInt(3, pk.getWorktypeId());
            int rows = stmt.executeUpdate();
            reset(dto);
            long t2 = System.currentTimeMillis();
            if (logger.isDebugEnabled()) {
                logger.debug(rows + " rows affected (" + (t2 - t1) + " ms)");
            }

        }
        catch (Exception _e) {
            logger.error("Exception: " + _e.getMessage(), _e);
            throw new HsWorktypeDaoException("Exception: " + _e.getMessage(), _e);
        }
        finally {
            ResourceManager.close(stmt);
            if (!isConnSupplied) {
                ResourceManager.close(conn);
            }

        }

    }

    /**
     * Deletes a single row in the HS_WorkType table.
     */
    public void delete(HsWorktypePk pk) throws HsWorktypeDaoException {
        long t1 = System.currentTimeMillis();
        // declare variables
        final boolean isConnSupplied = (userConn != null);
        Connection conn = null;
        PreparedStatement stmt = null;

        try {
            // get the user-specified connection or get a connection from the ResourceManager
            conn = isConnSupplied ? userConn : ResourceManager.getConnection();

            if (logger.isDebugEnabled()) {
                logger.debug("Executing " + SQL_DELETE + " with PK: " + pk);
            }

            stmt = conn.prepareStatement(SQL_DELETE);
            stmt.setInt(1, pk.getWorktypeId());
            int rows = stmt.executeUpdate();
            long t2 = System.currentTimeMillis();
            if (logger.isDebugEnabled()) {
                logger.debug(rows + " rows affected (" + (t2 - t1) + " ms)");
            }

        }
        catch (Exception _e) {
            logger.error("Exception: " + _e.getMessage(), _e);
            throw new HsWorktypeDaoException("Exception: " + _e.getMessage(), _e);
        }
        finally {
            ResourceManager.close(stmt);
            if (!isConnSupplied) {
                ResourceManager.close(conn);
            }

        }

    }

    /**
     * Returns the rows from the HS_WorkType table that matches the specified primary-key value.
     */
    public HsWorktype findByPrimaryKey(HsWorktypePk pk) throws HsWorktypeDaoException {
        return findByPrimaryKey(pk.getWorktypeId());
    }

    /**
     * Returns all rows from the HS_WorkType table that match the criteria 'workType_id = :worktypeId'.
     */
    public HsWorktype findByPrimaryKey(int worktypeId) throws HsWorktypeDaoException {
        HsWorktype ret[] = findByDynamicSelect(SQL_SELECT + " WHERE workType_id = ?", new Object[]{new Integer(worktypeId)});
        return ret.length == 0 ? null : ret[0];
    }

    /**
     * Returns all rows from the HS_WorkType table that match the criteria ''.
     */
    public HsWorktype[] findAll() throws HsWorktypeDaoException {
        return findByDynamicSelect(SQL_SELECT + " ORDER BY workType_id", null);
    }

    /**
     * Returns all rows from the HS_WorkType table that match the criteria 'workType_id = :worktypeId'.
     */
    public HsWorktype[] findWhereWorktypeIdEquals(int worktypeId) throws HsWorktypeDaoException {
        return findByDynamicSelect(SQL_SELECT + " WHERE workType_id = ? ORDER BY workType_id", new Object[]{new Integer(worktypeId)});
    }

    /**
     * Returns all rows from the HS_WorkType table that match the criteria 'name = :name'.
     */
    public HsWorktype[] findWhereNameEquals(String name) throws HsWorktypeDaoException {
        return findByDynamicSelect(SQL_SELECT + " WHERE name = ? ORDER BY name", new Object[]{name});
    }

    /**
     * Method 'HsWorktypeDaoImpl'
     */
    public HsWorktypeDaoImpl() {
    }

    /**
     * Method 'HsWorktypeDaoImpl'
     *
     * @param userConn
     */
    public HsWorktypeDaoImpl(final java.sql.Connection userConn) {
        this.userConn = userConn;
    }

    /**
     * Sets the value of maxRows
     */
    public void setMaxRows(int maxRows) {
        this.maxRows = maxRows;
    }

    /**
     * Gets the value of maxRows
     */
    public int getMaxRows() {
        return maxRows;
    }

    /**
     * Method 'getTableName'
     *
     * @return String
     */
    public String getTableName() {
        return "HS_WorkType";
    }

    /**
     * Fetches a single row from the result set
     */
    protected HsWorktype fetchSingleResult(ResultSet rs) throws SQLException {
        if (rs.next()) {
            HsWorktype dto = new HsWorktype();
            populateDto(dto, rs);
            return dto;
        } else {
            return null;
        }

    }

    /**
     * Fetches multiple rows from the result set
     */
    protected HsWorktype[] fetchMultiResults(ResultSet rs) throws SQLException {
        Collection resultList = new ArrayList();
        while (rs.next()) {
            HsWorktype dto = new HsWorktype();
            populateDto(dto, rs);
            resultList.add(dto);
        }

        HsWorktype ret[] = new HsWorktype[resultList.size()];
        resultList.toArray(ret);
        return ret;
    }

    /**
     * Populates a DTO with data from a ResultSet
     */
    protected void populateDto(HsWorktype dto, ResultSet rs) throws SQLException {
        dto.setWorktypeId(rs.getInt(COLUMN_WORKTYPE_ID));
        dto.setName(rs.getString(COLUMN_NAME));
    }

    /**
     * Resets the modified attributes in the DTO
     */
    protected void reset(HsWorktype dto) {
    }

    /**
     * Returns all rows from the HS_WorkType table that match the specified arbitrary SQL statement
     */
    public HsWorktype[] findByDynamicSelect(String sql, Object[] sqlParams) throws HsWorktypeDaoException {
        // declare variables
        final boolean isConnSupplied = (userConn != null);
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {
            // get the user-specified connection or get a connection from the ResourceManager
            conn = isConnSupplied ? userConn : ResourceManager.getConnection();

            // construct the SQL statement
            final String SQL = sql;


            if (logger.isDebugEnabled()) {
                logger.debug("Executing " + SQL);
            }

            // prepare statement
            stmt = conn.prepareStatement(SQL);
            stmt.setMaxRows(maxRows);

            // bind parameters
            for (int i = 0; sqlParams != null && i < sqlParams.length; i++) {
                stmt.setObject(i + 1, sqlParams[i]);
            }


            rs = stmt.executeQuery();

            // fetch the results
            return fetchMultiResults(rs);
        }
        catch (Exception _e) {
            logger.error("Exception: " + _e.getMessage(), _e);
            throw new HsWorktypeDaoException("Exception: " + _e.getMessage(), _e);
        }
        finally {
            ResourceManager.close(rs);
            ResourceManager.close(stmt);
            if (!isConnSupplied) {
                ResourceManager.close(conn);
            }

        }

    }

    /**
     * Returns all rows from the HS_WorkType table that match the specified arbitrary SQL statement
     */
    public HsWorktype[] findByDynamicWhere(String sql, Object[] sqlParams) throws HsWorktypeDaoException {
        // declare variables
        final boolean isConnSupplied = (userConn != null);
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        try {
            // get the user-specified connection or get a connection from the ResourceManager
            conn = isConnSupplied ? userConn : ResourceManager.getConnection();

            // construct the SQL statement
            final String SQL = SQL_SELECT + " WHERE " + sql;


            if (logger.isDebugEnabled()) {
                logger.debug("Executing " + SQL);
            }

            // prepare statement
            stmt = conn.prepareStatement(SQL);
            stmt.setMaxRows(maxRows);

            // bind parameters
            for (int i = 0; sqlParams != null && i < sqlParams.length; i++) {
                stmt.setObject(i + 1, sqlParams[i]);
            }


            rs = stmt.executeQuery();

            // fetch the results
            return fetchMultiResults(rs);
        }
        catch (Exception _e) {
            logger.error("Exception: " + _e.getMessage(), _e);
            throw new HsWorktypeDaoException("Exception: " + _e.getMessage(), _e);
        }
        finally {
            ResourceManager.close(rs);
            ResourceManager.close(stmt);
            if (!isConnSupplied) {
                ResourceManager.close(conn);
            }

        }

    }

}
